# coding:utf-8
import pandas as pd
import csv
import numpy as np
from datetime import datetime
import pymysql

# 读取文件
# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="hui123456", db='dbtest')
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()


sql = """
      SELECT order2,
             Product_ID,
             Product_xg,
             Product_BianMa,
             Product_num,
             Product_price,
             liulianglaiyuan,
             daren,
             daren_id,
             guanggao,
             liuliangleixing,
             liuliangqudao,
             liuliangticai, day, yh_pingtai, yh_daren, shop, order_zt, order_sh, fuai, dianzhuguo
      FROM doudian_day
      WHERE
          MONTH (day) = 10
        AND day != "1998-01-01" \
      """

cursor.execute(sql)
result = cursor.fetchall()
dou = pd.DataFrame(list(result))

dou.columns = ['订单id', '商品ID', '选购商品', '商家编码', '商品数量', '订单应付金额', '流量来源', '达人昵称', '达人ID',
               '广告渠道', '流量类型', '流量渠道', '流量体裁', '订单提交时间', '平台实际承担优惠金额',
               '达人实际承担优惠金额', '店铺', '订单状态', '售后状态', '福袋', '电煮锅']

dou['成交金额_含优惠'] = np.where(((dou['订单状态'] == "已签收") | (dou['订单状态'] == "待发货") | (
        dou['订单状态'] == "已完成") | (dou['订单状态'] == "已发货") | (dou['订单状态'] == "已支付") | (
                                           dou['订单状态'] == "部分发货"))
                                  & (dou['售后状态'] != "退款成功") & (dou['售后状态'] != "售后完成") & (
                                          dou['售后状态'] != "退款完成") & (dou['售后状态'] != "已全额退款"),
                                  dou['订单应付金额'] + dou['平台实际承担优惠金额'] + dou['达人实际承担优惠金额'], 0)

dou['成交订单'] = np.where(((dou['订单状态'] == "已签收") | (dou['订单状态'] == "待发货") | (
        dou['订单状态'] == "已完成") | (dou['订单状态'] == "已发货") | (dou['订单状态'] == "已支付") | (
                                    dou['订单状态'] == "部分发货"))
                           & (dou['售后状态'] != "退款成功") & (dou['售后状态'] != "售后完成") & (
                                   dou['售后状态'] != "退款完成") & (dou['售后状态'] != "已全额退款"), 1, 0)

dou['退款金额_含优惠'] = np.where(
    (dou['售后状态'] == '退款成功') | (dou['售后状态'] == '退款完成') | (dou['售后状态'] == '已全额退款') | (
            dou['售后状态'] == '售后完成') | (dou['售后状态'] == '退货退款完成') | (dou['售后状态'] == '交易关闭'),
    dou['订单应付金额'] + dou['平台实际承担优惠金额'] + dou['达人实际承担优惠金额'], 0)

ld = pd.read_excel(r'G:\工作\2025年订单\10月\绩效\刘栋达人.xlsx', dtype={'抖音号名称': str})
lxx1 = pd.read_excel(r'G:\工作\2025年订单\10月\绩效\罗欣欣投流达人.xlsx', dtype={'抖音号名称': str})  # (桶装)（袋装）
bpf2 = pd.read_excel(r'G:\工作\2025年订单\10月\绩效\白鹏飞投流达人.xlsx', dtype={'抖音号名称': str})  # (土豆粉)
xhwh1 = pd.read_excel(r'G:\工作\2025年订单\10月\绩效\夕湖文化.xlsx', dtype={'抖音号名称': str})  # (土豆粉)
hn1 = pd.read_excel(r'G:\工作\2025年订单\10月\绩效\花妞.xlsx', dtype={'抖音号名称': str})  # (土豆粉)
px1 = pd.read_excel(r'G:\工作\2025年订单\10月\绩效\飘雪.xlsx', dtype={'抖音号名称': str})

z1 = pd.read_excel(r'G:\工作\2025年订单\10月\招商1.xlsx', dtype={'订单id': str})
z2 = pd.read_excel(r'G:\工作\2025年订单\10月\招商2.xlsx', dtype={'订单id': str})
z3 = pd.read_excel(r'G:\工作\2025年订单\10月\招商3.xlsx', dtype={'订单id': str})
z4 = pd.read_excel(r'G:\工作\2025年订单\10月\招商4.xlsx', dtype={'订单id': str})


y1 = pd.read_excel(r'G:\工作\2025年订单\10月\佣金1.xlsx', dtype={'订单id': str})
y2 = pd.read_excel(r'G:\工作\2025年订单\10月\佣金2.xlsx', dtype={'订单id': str})
y3 = pd.read_excel(r'G:\工作\2025年订单\10月\佣金3.xlsx', dtype={'订单id': str})
y4 = pd.read_excel(r'G:\工作\2025年订单\10月\佣金4.xlsx', dtype={'订单id': str})
y = pd.concat([y1, y2, y3, y4])
# 匹配佣金表，达人昵称
y_date = y.loc[:, ['订单id', '作者账号', '预估佣金支出']]
y_date['订单id'] = y_date['订单id'].str.strip()

dou_date = pd.merge(dou, y_date, on='订单id', how='left')
# dou_date['达人昵称'].fill(dou_date['作者账号'],inplace=True)
dou_date['达人昵称'] = np.where(dou_date['达人昵称'] == 'nan', dou_date['作者账号'], dou_date['达人昵称'])
# 招商表
z1_date = z1.loc[:, ['订单id', '出单机构', '预估服务费收入']]
z2_date = z2.loc[:, ['订单id', '出单机构', '预估服务费收入']]
z3_date = z3.loc[:, ['订单id', '出单机构', '预估服务费收入']]
z4_date = z4.loc[:, ['订单id', '出单机构', '预估服务费收入']]
zs = pd.concat([z1_date, z2_date, z3_date, z4_date])

dou_res = pd.merge(dou_date, zs, on='订单id', how='left')

# 各投流链接,达人

lc_lj = ['3752066905167495351', '3754479940293230816']  # 良辰链接
lc_dr = []  # 良辰达人

ld_lj = ['3524421208374397998', '3718871706278953300', '3764710268731523333']  # 刘栋单独链接
ld_dr = list(ld['抖音号名称'])

lxx1_lj = ['3753012046921335096', '3701090938974765405', '3506957255406950391', '3520740088860524483',
           '3758543932670214558', '3775109632327024899', '3751738608588030256', '3752292420973363354',
           '3754689465759694891']
lxx1_dr = list(lxx1['抖音号名称'])  # 罗欣欣达人

bpf2_lj = ['3781986063870984612', '3758958156538052831', '3506957255406950391', '3701090938974765405',
           '3753012046921335096', '3775109632327024899', '3758543932670214558', '3751738608588030256',
           '3767113766748160259', '3783048254388830370', '3754689465759694891', '3687141961262825560',
           '3674502993446830541', '3737638082678227388', '3694732441748111734', '3628547567660489014', ]
bpf2_dr = list(bpf2['抖音号名称'])  # 白鹏飞达人

xhwh1_lj = ['3751738608588030256', '3723151364247912548']  # 夕湖文化
xhwh1_dr = list(xhwh1['抖音号名称'])

hn1_lj = ['3701090938974765405', '3758958156538052831', '3687141961262825560', '3775109632327024899']  # 花妞千川——爆品侠
hn1_dr = list(hn1['抖音号名称'])

px1_lj = ['3751738608588030256']  # 飘雪
px1_dr = list(px1['抖音号名称'])


def f(x, y, q, z, v, s, w, p):  # x: 链接 #y: 达人昵称 #q:达人ID #z:选购商品 #v:广告渠道 #s:流量来源 #w:出单机构 #p:店铺
    if w in ['武汉海大电子商务有限公司']:
        return '嗨大'

    elif w in ['卓敏科技', '紫金优选', '瀚睿科技', '红利传媒', '来吉团队', '金莎传媒']:
        return '冷漠'


    elif (x in ld_lj) and ((y in ld_dr) or (pd.isnull(y))):  # 去掉招商达人，加上自然单
        return '刘栋单独链接'
    elif (x in ['3751738608588030256']) and (y in ld_dr):
        return '刘栋'

    elif y in ['harry5']:
        return 'harry5'

    elif q in ['69526924387']:
        return '喜喜投流大号'

    elif q in ['100954967066']:
        return '阿啦蕾'

    elif y in ['饱饱吃不饱']:  # 罗欣欣运营账号
        return '罗欣欣账号'

    elif y in ['啦啦啦lalala', '怪咖美食记', '爱吃的光远', '空壳少年', '食光里的馋猫🐱', '宛禾食品甄选号',
               '金金会发光', '味蕾探险家', '饭桶爱说笑', '大馋丫头可可']:  # 白鹏飞运营账号
        return '白鹏飞账号'
    elif (x in xhwh1_lj) and (y in xhwh1_dr):
        return '夕湖文化'

    elif (x in px1_lj) and (y in px1_dr):
        return '飘雪'

    elif (x in lxx1_lj) and (y in lxx1_dr):
        return '罗欣欣投流达人'

    elif (x in bpf2_lj) and (y in bpf2_dr):
        return '白鹏飞投流达人'
    elif (x in hn1_lj) and (y in hn1_dr):
        return '代投花妞'

    elif (x in ['3751738608588030256']) and (y in hn1_dr):
        return '花妞'
    elif (x in lc_lj):
        return '莨辰'

    elif x in ['3712805099375034797', '3735010733180715297']:  # 罗欣欣单独链接
        return '罗欣欣账号'
    elif x in ['3741923461510070306', '3723119143235485902', '3741926701987398242',
               '3550782895822129982', '3526108849595662124', '3699396698997653933']:  # 白鹏飞单独链接
        return '白鹏飞账号'

    elif x in ['3714480675638870403']:
        return '共用链接自然单'


    elif y == '宛禾米线官方旗舰店' or ('z1' in z) or (' Z1' in z):
        return '自播1'
    elif y == '宛禾米线速食官方旗舰店' or y == '宛禾米线' or y == '宛禾速食官方旗舰店' or y == '宛禾速食直播间' or (
            'z3' in z) or (' Z3' in z):
        return '自播3'
    elif y == '宛禾米线速食旗舰店' or ('lsf' in z):
        return '螺蛳土豆粉直播间'
    elif y == '宛禾食品旗舰店' or ('z5' in z) or ('Z5' in z) or ('zb' in x):
        return '自播5'
    elif y == '宛禾速食旗舰店' or y == '宛禾食品旗舰店甄选号' or ('z6' in z) or ('Z6' in z):
        return '自播6'
    elif y == '宛禾官方旗舰店' or ('z8' in z) or (' Z8' in z):
        return '自播8'
    elif p == '抖音1' and ('dsp' in z):
        return '自播1'
    elif p == '抖音2' and ('dsp' in z):
        return '自播5'
    elif p == '抖音3' and ('dsp' in z):
        return '自播8'
    elif p == '抖音4' and ('dsp' in z):
        return '螺蛳土豆粉直播间'

    elif (v == '商品卡'):  # 投流商品卡
        return '超级商品卡'

    elif pd.notnull(y):
        return '达人分发'
    elif s == '精选联盟':
        return '达人分发'

    else:
        return '自然单'


dou_res['投流账户'] = list(
    map(f, list(dou_res['商品ID']), list(dou_res['达人昵称']), list(dou_res['达人ID']), list(dou_res['选购商品']),
        list(dou_res['广告渠道']), list(dou_res['流量来源']), list(dou_res['出单机构']), list(dou_res['店铺'])))


def m(x, y, q, z, v, s, w):  # x: 链接 #y: 达人昵称 #q:达人ID #z:选购商品 #v:广告渠道 #s:流量来源 #w:出单机构
    if w in ['武汉海大电子商务有限公司']:
        return '外部团队达人分发'

    elif w in ['卓敏科技', '紫金优选', '瀚睿科技', '红利传媒', '来吉团队', '金莎传媒']:
        return '外部团队达人分发'



    elif (x in ld_lj) and ((y in ld_dr) or (pd.isnull(y))):
        return '内部短视频投流'
    elif (x in ['3751738608588030256']) and (y in ld_dr):
        return '内部短视频投流'

    elif y in ['harry5']:
        return '内部达人分发'

    elif q in ['69526924387']:
        return '内部达人分发'
    elif q in ['100954967066']:
        return '内部达人分发'

    elif y in ['饱饱吃不饱']:  # 罗欣欣运营账号
        return '内部短视频投流'

    elif y in ['啦啦啦lalala', '怪咖美食记', '爱吃的光远', '空壳少年', '食光里的馋猫🐱', '宛禾食品甄选号',
               '金金会发光', '味蕾探险家', '饭桶爱说笑', '大馋丫头可可']:  # 白鹏飞运营账号
        return '内部短视频投流'

    elif (x in xhwh1_lj) and (y in xhwh1_dr):
        return '外部短视频投流'

    elif (x in px1_lj) and (y in px1_dr):
        return '外部短视频投流'

    elif (x in lxx1_lj) and (y in lxx1_dr):
        return '内部达人分发'

    elif (x in bpf2_lj) and (y in bpf2_dr):
        return '内部达人分发'

    elif (x in hn1_lj) and (y in hn1_dr):
        return '外部短视频投流'
    elif (x in ['3751738608588030256']) and (y in hn1_dr):
        return '外部短视频投流'
    elif (x in lc_lj):
        return '外部短视频投流'

    elif x in ['3712805099375034797', '3735010733180715297']:  # 罗欣欣单独链接
        return '内部短视频投流'
    elif x in ['3741923461510070306', '3723119143235485902', '3741926701987398242',
               '3550782895822129982', '3526108849595662124', '3699396698997653933']:  # 白鹏飞单独链接
        return '内部短视频投流'

    elif x in ['3714480675638870403']:
        return '内部短视频投流'


    elif y == '宛禾米线官方旗舰店' or ('z1' in z) or ('Z1' in z):
        return '自播'
    elif y == '宛禾米线速食官方旗舰店' or y == '宛禾米线' or y == '宛禾速食官方旗舰店' or y == '宛禾速食直播间' or y == '宛禾速食' or (
            'z3' in z) or ('Z3' in z):
        return '自播'
    elif y == '宛禾米线速食旗舰店' or ('lsf' in z):
        return '自播'
    elif y == '宛禾食品旗舰店' or ('z5' in z) or ('Z5' in z) or ('dsp' in z):
        return '自播'
    elif y == '宛禾速食旗舰店' or y == '宛禾食品旗舰店甄选号' or ('z6' in z) or ('Z6' in z):
        return '自播'
    elif y == '宛禾官方旗舰店' or ('z8' in z) or ('Z8' in z):
        return '自播'

    elif (v == '商品卡'):  # 投流商品卡
        return '超级商品卡'


    elif pd.notnull(y):
        return '内部达人分发'
    elif s == '精选联盟':
        return '内部达人分发'
    else:
        return '自然单'


dou_res['总渠道'] = list(
    map(m, list(dou_res['商品ID']), list(dou_res['达人昵称']), list(dou_res['达人ID']), list(dou_res['选购商品']),
        list(dou_res['广告渠道']), list(dou_res['流量来源']), list(dou_res['出单机构'])))

result = dou_res.groupby([dou_res['总渠道'], dou_res['投流账户']], as_index=False).agg(
    销售额_含优惠去退款=('成交金额_含优惠', 'sum'), 订单数=('成交订单', 'sum'), 退款金额=('退款金额_含优惠', 'sum'))

result.to_excel(r'G:\工作\2025年订单\10月\绩效\10月各渠道销售额月度.xlsx')

date_SKU = dou_res
result_sku = date_SKU.groupby([date_SKU['商品ID'], date_SKU['总渠道'], date_SKU['投流账户']], as_index=False).agg(
    销售额_含优惠去退款=('成交金额_含优惠', 'sum'), 订单数=('成交订单', 'sum'), 退款金额=('退款金额_含优惠', 'sum'))

result_sku.to_excel(r'G:\工作\2025年订单\10月\绩效\10月投流销售额SKU月度.xlsx')

result_bm = date_SKU.groupby([date_SKU['商家编码'], date_SKU['总渠道'], date_SKU['投流账户']], as_index=False).agg(
    销售额_含优惠去退款=('成交金额_含优惠', 'sum'), 订单数=('成交订单', 'sum'), 退款金额=('退款金额_含优惠', 'sum'))

result_bm.to_excel(r'G:\工作\2025年订单\10月\绩效\10月投流销售额编码月度.xlsx')

dl = dou_res[(dou_res['成交订单'] != 0) & (dou_res['总渠道'] != '自播') & (dou_res['总渠道'] != '代播') & (
        dou_res['总渠道'] != '自然单')]

dl.to_excel(r'G:\工作\2025年订单\10月\绩效\10月投流绩效单量明细月度.xlsx')

# 利润测算

dou_li = dou_res[(dou_res['成交订单'] != 0)]
dou_ = dou_li.groupby(
    [dou_li['店铺'], dou_li['总渠道'], dou_li['投流账户'], dou_li['商品ID'], dou_li['商家编码'], dou_li['福袋'],
     dou_li['电煮锅']], as_index=False).agg(订单实付=('订单应付金额', 'sum'), 商品数量=('商品数量', 'sum'),
                                            平台优惠=('平台实际承担优惠金额', 'sum'),
                                            达人优惠=('达人实际承担优惠金额', 'sum'),
                                            成交金额=('成交金额_含优惠', 'sum'), 佣金=('预估佣金支出', 'sum'),
                                            招商佣金=('预估服务费收入', 'sum'))
dou_.to_excel(r'G:\工作\SKU利润测算\2025年\10月\10月利润测算明细_抖音.xlsx')

orderid = dou_res.loc[:, ['订单id', '总渠道', '订单提交时间', '店铺']]
# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="hui123456", db='dbtest')
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()


sqll = ('insert into orderID_qd values (%s,%s,%s,%s)')

for val in orderid.values:
    cursor.execute(sqll, tuple(val))

db.commit()
db.close()
